Help with Writing a Recursive Query in SQL Server

Question

Ravi Vishwakarma · Answer

Recursive queries in SQL Server are typically written using 
Common Table Expressions (CTEs). These are particularly useful for hierarchical data, such as organizational charts, family trees, or any dataset that has a parent-child relationship.
Here's a basic example to illustrate how to write a recursive query using a CTE in SQL Server. We'll use a hypothetical organizational structure as an example.
Example: Organizational Hierarchy
Assume we have a table named Employees with the following structure:
EmployeeID
EmployeeName
ManagerID
1
Alice
NULL
2
Bob
1
3
Carol
1
4
Dave
2
5
Eve
2
In this table:
EmployeeID is the unique identifier for each employee.EmployeeName is the name of the employee.ManagerID is the EmployeeID of the employee's manager. The top-level manager (Alice) has a
NULL ManagerID.
Recursive Query
Retrieve the entire hierarchy starting from the top-level manager.
Here's how you can write a recursive CTE to achieve this:
WITH EmployeeCTE AS (
    -- Anchor member: this query defines the top-level manager(s)
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level
    FROM 
        Employees
    WHERE 
        ManagerID IS NULL
    UNION ALL
    -- Recursive member: this query joins the CTE with the Employees table
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        c.Level + 1
    FROM 
        Employees e
    INNER JOIN 
        EmployeeCTE c ON e.ManagerID = c.EmployeeID
)
-- Select from the CTE
SELECT 
    EmployeeID,
    EmployeeName,
    ManagerID,
    Level
FROM 
    EmployeeCTE
ORDER BY 
    Level, EmployeeID;
Explanation:
Anchor Member:
The first SELECT statement in the CTE defines the anchor member, which retrieves the top-level manager(s) (those with
NULL ManagerID).
Recursive Member:
The second SELECT statement in the CTE joins the Employees table with the
EmployeeCTE on ManagerID and EmployeeID, effectively retrieving the employees managed by each employee in the
EmployeeCTE.Level is incremented by 1 to indicate the hierarchy level.
Final SELECT:
The final SELECT statement retrieves the data from the CTE, and the
ORDER BY clause is used to sort the results by hierarchy level and 
EmployeeID.
This recursive query will produce the hierarchical structure of the organization, starting from the top-level manager down to the lowest level.
Read more
Define the PIVOT Table with examples in the SQL server.
Explain the SQL triggers and their uses
Explain the SQL Server backups and their types

forum

Help with Writing a Recursive Query in SQL Server

Anubhav Kumar

Can you answer this question?

1 Answers

Recursive Query

Liked By